﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel;

namespace Deanery.Controller.Service
{
    class Import
    {
        public DataSet FromExcel()
        {
            var opf = new OpenFileDialog
                {
                    Filter = @"Всі файли Excel| *.xls; *.xlsx"
                };
            opf.ShowDialog();

            var filePath = opf.FileName;
            if (filePath != string.Empty)
            {
                FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
                IExcelDataReader excelReader = null;

                var extension = Path.GetExtension(filePath);
                switch (extension)
                {
                    case ".xls":
                        {
                            excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
                            break;
                        }

                    case ".xlsx":
                        {
                            excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                            break;
                        }
                }


                DataSet dataSet = excelReader.AsDataSet();
                excelReader.Close();

                var flag = true;
                var counter = 0;
                for (var i = 0; i < dataSet.Tables[0].Rows.Count; i++)
                {
                    if ((dataSet.Tables[0].Rows[i][0].ToString().Trim() != "1") && flag)
                        counter++;
                    else
                    {
                        flag = false;
                    }
                }

                for (var i = 0; i < counter; i++)
                    dataSet.Tables[0].Rows.RemoveAt(0);

                return dataSet;
            }
            return null;
        }
    }
}
